package sshj.excelutil;

import conv.AssetBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 读取exlce内容工具类.
 *
 * @author chenchunfeng
 * 2018年3月12日
 */
public class ExcelReaderUtil {
    /**
     * 日志对象.
     */
    private static final Logger LOG = LoggerFactory.getLogger(ExcelReaderUtil.class);
    /**
     * poi文件流.
     */
    private POIFSFileSystem fs;
    /**
     * excle工作薄.
     */
    private HSSFWorkbook wb;
    /**
     * sheet对象.
     */
    private HSSFSheet sheet;
    /**
     * row 对象.
     */
    private HSSFRow row;

    public ExcelReaderUtil(POIFSFileSystem fs, HSSFWorkbook wb) {
        this.fs = fs;
        this.wb = wb;
    }

    /**
     * 根据HSSFCell类型设置数据.
     *
     * @param cell 列内容
     * @return String
     */
    @SuppressWarnings("deprecation")
    private static String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为number
                case HSSFCell.CELL_TYPE_NUMERIC:

                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是够为date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是date类型,则转化为Date格式
                        // date格式是带时分秒格式的
                        // cellvalue = cell.getDateCellValue().toLocaleString();
                        // 不带时分秒格式的
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    }
                    // 如果是纯数字
                    else {
                        // 取得当前cell的数值
                        cellvalue = subNumberStr(String.valueOf(cell.getNumericCellValue()));
                    }
                    break;
                }
                // 如果当前cell数据类型为字符串
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    public static String subNumberStr(String str) {
        if (str.indexOf(".") > 0) {
            //去掉多余的0
            str = str.replaceAll("0+?$", "");
            //如果最后一位是. 去掉
            str = str.replaceAll("[.]$", "");
        }
        return str;
    }

    /**
     * 读取exlce内容.
     *
     * @return Map 单元格map对象
     */
    @SuppressWarnings("deprecation")
    public List<AssetBean> readExcelContent() {
        List<AssetBean> sshBeans = new ArrayList<AssetBean>();
        List<Map<Integer, String>> contentList = new ArrayList<Map<Integer, String>>();
        try {
            Set<String> xsets = new HashSet<String>();
            sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            row = sheet.getRow(0);
//            int colNum = row.getPhysicalNumberOfCells();
            // 循环遍历获取exlce内容
            for (int i = 0; i <= rowNum; i++) {
                row = sheet.getRow(i);
                AssetBean assetBean = new AssetBean();
                String ipmiIP = getCellFormatValue(row.getCell(9)).trim();
                if (!ipmiIP.equals("") && ipmiIP.startsWith("10")) {
//                    assetBean.setIpmiIP(getCellFormatValue(row.getCell(9)).trim());
                    assetBean.setProtocol("SSH");
//                    assetBean.setLabel("");
                    assetBean.setOsIP(getCellFormatValue(row.getCell(8)).trim());
                    assetBean.setOsPasswd("nsccjn@2020");
                    assetBean.setOsUser("root");
                    assetBean.setOsPort("22");
                    //assetBean.setIpmiName("admin");
//                    assetBean.setIpmiPass("admin");
//                    assetBean.setDataCenter("国家超算济南中心");
//                    assetBean.setRoom(getCellFormatValue(row.getCell(0)).trim());
//                    assetBean.setJigui(getCellFormatValue(row.getCell(1)).trim());
//                    assetBean.setPosition(getCellFormatValue(row.getCell(2)).trim());
//                    assetBean.setName(assetBean.getIpmiIP());
//                    assetBean.setHostname(getCellFormatValue(row.getCell(6)).trim());
                    xsets.add(assetBean.getJigui());
                    sshBeans.add(assetBean);
                }

            }
            // System.out.println(xsets);
            // exportJiGui(xsets);
        } catch (Exception e) {
            LOG.error("explain excel error：" + e);
        }
        return sshBeans;
    }

    public void exportJiGui(Set<String> xsets) {
        List<String> jgList = new ArrayList<String>(xsets);
        Collections.sort(jgList);
        int startRow = 3;
        String templateName = "JG.xlsx";
        String templatePath = "E:\\ns\\";
        File templateFile = new File(templatePath + templateName);
        if (templateFile.exists()) {
            String destName = "JG1.xlsx";
            try {
                com.inspur.pim.cbb.maintenance.util.FileUtils.copyFile(templatePath + destName, templatePath + templateName);
            } catch (Exception ignored) {
            }
            File destFile = new File(templatePath + destName);
            try {
                InputStream is = new FileInputStream(templateFile);

                FileOutputStream os = new FileOutputStream(destFile);
                XSSFWorkbook wb = new XSSFWorkbook(is);
                XSSFSheet sheet = wb.getSheetAt(0);
                if (jgList.size() > 0) {
                    for (int i = startRow; i < startRow + jgList.size(); i++) {
                        String jgName = jgList.get(i - startRow);
                        XSSFRow row = sheet.getRow(i);
                        if (row == null) {
                            row = sheet.createRow(i);
                        }
                        for (int j = 0; j < 2; j++) {
                            Cell cell = row.getCell(j);
                            if (cell == null) {
                                cell = row.createCell(j);
                            }
                            if (null != jgName && !jgName.equals("")) {
                                cell.setCellValue(jgName);
                            }
                        }
                    }
                }
                wb.write(os);
                os.flush();
                is.close();

            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

    public void export(List<AssetBean> assetLists) {
        int startRow = 4;
        String templateName = "T1.xlsx";
        String templatePath = "E:\\ns\\";
        File templateFile = new File(templatePath + templateName);
        if (templateFile.exists()) {
            String destName = "D1OS.xlsx";
            try {
                com.inspur.pim.cbb.maintenance.util.FileUtils.copyFile(templatePath + destName, templatePath + templateName);
            } catch (Exception ignored) {
            }
            File destFile = new File(templatePath + destName);
            try {
                InputStream is = new FileInputStream(templateFile);

                FileOutputStream os = new FileOutputStream(destFile);
                XSSFWorkbook wb = new XSSFWorkbook(is);
                XSSFSheet sheet = wb.getSheetAt(0);
                if (null != assetLists) {
                    for (int i = startRow; i < startRow + assetLists.size(); i++) {
                        AssetBean assetBean = assetLists.get(i - startRow);
                        XSSFRow row = sheet.getRow(i);
                        if (row == null) {
                            row = sheet.createRow(i);
                        }
                        for (int j = 0; j < 35; j++) {
                            Cell cell = row.getCell(j);
                            if (cell == null) {
                                cell = row.createCell(j);
                            }
                            String value = getCellValue(assetBean, j);
                            if (null != value && !value.equals("")) {
                                cell.setCellValue(value);
                            }
                        }
                    }
                }
                wb.write(os);
                os.flush();
                is.close();

            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }


    private String getCellValue(AssetBean serverBean, int j) {
        String value = "";
        switch (j) {
            case 0: {
                value = serverBean.getName();
                break;
            }
            case 1: {
                value = serverBean.getIpmiIP();
                break;
            }
            case 2: {
                value = serverBean.getProtocol();
                break;
            }
            case 3: {
                value = serverBean.getVendor();
                break;
            }
            case 4: {
                value = serverBean.getModel();
                break;
            }
            case 5: {
                value = serverBean.getLabel();
                break;
            }
            case 8: {
                value = serverBean.getOsIP();
                break;
            }
            case 9: {
                value = serverBean.getHostname();
                break;
            }
            case 10: {
                value = serverBean.getOs();
                break;
            }
            case 12: {
                value = serverBean.getIpmiName();
                break;
            }
            case 13: {
                value = serverBean.getIpmiPass();
                break;

            }
            case 26: {
                value = serverBean.getOsUser();
                break;
            }
            case 27: {
                value = serverBean.getOsPasswd();
                break;
            }
            case 28: {
                value = serverBean.getOsPort();
                break;
            }
            case 29: {
                value = serverBean.getDataCenter();
                break;
            }
            case 30: {
                value = serverBean.getRoom();
                break;
            }
            case 31: {
                value = serverBean.getJigui();
                break;

            }
            case 32: {
                value = serverBean.getPosition();
                break;

            }

            default: {
                break;
            }
        }
        return value;
    }
}